import pandas as pd

import dolphindb as ddb
from utilities.detail_analyse_tools import detail_analyse
from utilities.plot_correlation import calculate_point_biserial_correlation

s = ddb.session("192.168.200.179", 8832, "chenzhimin", "suhhgjy98y_JHg87")

#
result = s.run("""
stk_zt = select ts_code as secu_code, trade_date as trading_date, * from load_table_from_mysql("select * from  XBBASE.stk_zt")

stk_price = select lead(close_price) as t1_close_price, lag(turnover_volume) as tm1_turnover_volume,mavg(turnover_volume, 5) as m5d_turnover_volume, lead(turnover_volume) as t1_turnover_volume, lead(change_pct) as t1_change_pct, lag(change_pct) as tm1_change_pct, move(open_price, 2) as t2_open_price, (move(close_price, -2)-close_price)\close_price as t2_change_pct, (move(close_price, -3)-close_price)\close_price as t3_change_pct, (move(close_price, -4)-close_price)\close_price as t4_change_pct, (move(close_price, -5)-close_price)\close_price as t5_change_pct, move(high_price, 2) as t2_high_price, lead(high_price) as t1_high_price, lead(low_price) as t1_low_price, lead(high_price)/close_price-1 as t1_high_return, move(close_price,5) as tm_5_close_price, move(close_price,3) as tm_3_close_price, move(close_price,20) as tm_20_close_price, move(close_price, -20) as t_20_close_price, move(close_price, -10) as t_10_close_price, move(close_price, -15) as t_15_close_price, move(close_price,-5) as t5_close_price,move(close_price,-6) as t6_close_price, end_date as trading_date, iif(lead(open_price) > close_price*1.01 and lead(high_price) >=1.02*close_price,1, 0) as next_high_open,  * from loadTable("dfs://ods_stock_quotation","ods_stock_quotation") where  end_date >= 2014.01.01 and if_trading_day = 1 context by secu_code csort end_date
stk_price = select ratio(close_price, tm_5_close_price) as last_week_return, ratio(close_price, tm_3_close_price) as last_3days_return, ratio(close_price, tm_20_close_price) as last_month_return, ratio(t5_close_price, close_price) as next_week_return, ratio(t6_close_price, t1_close_price) as t1_next_week_return, ratio(t_10_close_price, close_price) as next_2week_return, ratio(t_15_close_price, close_price) as next_3week_return, ratio(t_20_close_price, close_price) as next_month_return, ratio(high_price, pre_close_price)-1 as high_return, * from stk_price
stk_deleted = select * from loadTable("dfs://XBBASE_listed_company_list", "listed_company_list") where  not(secu_abbr like '%ST%') and not(listed_sector in ('科创板', '创业板'))

stk_price = select lag(last_week_return) as lag_last_week_return, * from ej(stk_price as s, stk_deleted as d, `secu_code) where trading_date >= 2014.01.01  context by secu_code csort trading_date

stk_zt = select * from ej(stk_price as s, stk_zt as z, `trading_date`secu_code)

select iif(t1_change_pct>0,1,-1) as t1_binary, limit_amount\\amount as fd_rate, *
from stk_zt
where limit_type=`D

""")


#
# result = s.run("""
#
# stk_price = select lead(close_price) as t1_close_price, lag(turnover_volume) as tm1_turnover_volume,mavg(turnover_volume, 5) as m5d_turnover_volume, lead(turnover_volume) as t1_turnover_volume, lead(change_pct) as t1_change_pct, lag(change_pct) as tm1_change_pct, move(open_price, 2) as t2_open_price, (move(close_price, -2)-close_price)\close_price as t2_change_pct, (move(close_price, -3)-close_price)\close_price as t3_change_pct, (move(close_price, -4)-close_price)\close_price as t4_change_pct, (move(close_price, -5)-close_price)\close_price as t5_change_pct, move(high_price, 2) as t2_high_price, lead(high_price) as t1_high_price, lead(low_price) as t1_low_price, lead(high_price)/close_price-1 as t1_high_return, move(close_price,5) as tm_5_close_price, move(close_price,3) as tm_3_close_price, move(close_price,20) as tm_20_close_price, move(close_price, -20) as t_20_close_price, move(close_price, -10) as t_10_close_price, move(close_price, -15) as t_15_close_price, move(close_price,-5) as t5_close_price,move(close_price,-6) as t6_close_price, end_date as trading_date, iif(lead(open_price) > close_price*1.01 and lead(high_price) >=1.02*close_price,1, 0) as next_high_open,  * from loadTable("dfs://ods_stock_quotation","ods_stock_quotation") where  end_date >= 2014.01.01 and if_trading_day = 1 context by secu_code csort end_date
# stk_price = select ratio(close_price, tm_5_close_price) as last_week_return, ratio(close_price, tm_3_close_price) as last_3days_return, ratio(close_price, tm_20_close_price) as last_month_return, ratio(t5_close_price, close_price) as next_week_return, ratio(t6_close_price, t1_close_price) as t1_next_week_return, ratio(t_10_close_price, close_price) as next_2week_return, ratio(t_15_close_price, close_price) as next_3week_return, ratio(t_20_close_price, close_price) as next_month_return, ratio(high_price, pre_close_price)-1 as high_return, * from stk_price
# stk_deleted = select * from loadTable("dfs://XBBASE_listed_company_list", "listed_company_list") where  not(secu_abbr like '%ST%') and not(listed_sector in ('科创板', '创业板'))
#
# stk_price = select lag(last_week_return) as lag_last_week_return, * from ej(stk_price as s, stk_deleted as d, `secu_code) where trading_date >= 2014.01.01  context by secu_code csort trading_date
#
#
# B=get_indicator_data_matrix(`1,`0,`6)
# x1 = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as t1_month_dde_turnover_value from loadTable("dfs://dwm_stock_factor_value_day", "dwm_stock_factor_value_day") where factor_id = `240400000001 order by end_date desc
# x2 = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as t1_day_dde_turnover_value from trans_for_doquery(stock_main_inflow_ratio(B, 1))
# x3 = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as t1_week_dde_turnover_value from trans_for_doquery(stock_main_inflow_ratio(B, 5))
# stk_price = select x.t1_month_dde_turnover_value, s.* from ej(stk_price as s, x1 as x, `trading_date`secu_code)
# stk_price = select x.t1_day_dde_turnover_value, s.* from ej(stk_price as s, x2 as x, `trading_date`secu_code)
# stk_price = select x.t1_week_dde_turnover_value, s.* from ej(stk_price as s, x3 as x, `trading_date`secu_code)
#
#
#
# select * from stk_price where trading_date >= 2014.01.01
#
# """)
# #
#

# result.to_csv('stk_price.csv', index=False)

# result = pd.read_csv('stk_price.csv' )
# print("######################### t1_change_pct #########################")
# detail_analyse(result, 't1_month_dde_turnover_value', 't1_change_pct', -0.2, 0.2, 10, 20)
#
# print("######################### t1_change_pct #########################")
# detail_analyse(result, 't1_week_dde_turnover_value', 't1_change_pct', -0.2, 0.2, 10, 20)
#
# print("######################### t1_change_pct #########################")
# detail_analyse(result, 't1_day_dde_turnover_value', 't1_change_pct', -0.2, 0.2, 10, 20)
#
#
#
# print("######################### t1_month_dde_turnover_value #########################")
# detail_analyse(result, 't1_month_dde_turnover_value', 't2_change_pct', -0.2, 0.2, 10, 20)
#
# print("######################### t1_week_dde_turnover_value #########################")
# detail_analyse(result, 't1_week_dde_turnover_value', 't2_change_pct', -0.2, 0.2, 10, 20)
#
# print("######################### t1_day_dde_turnover_value #########################")
# detail_analyse(result, 't1_day_dde_turnover_value', 't2_change_pct', -0.2, 0.2, 10, 20)



# print("######################### t1_month_dde_turnover_value & t3_change_pct #########################")
# detail_analyse(result, 't1_month_dde_turnover_value', 't2_change_pct', -0.2, 0.2, 10, 20)
#
# print("######################### t1_week_dde_turnover_value & t3_change_pct #########################")
# detail_analyse(result, 't1_week_dde_turnover_value', 't3_change_pct', -0.2, 0.2, 10, 20)
#
# print("######################### t1_day_dde_turnover_value & t3_change_pct #########################")
# detail_analyse(result, 't1_day_dde_turnover_value', 't3_change_pct', -0.2, 0.2, 10, 20)

# print("######################### t1_change_pct #########################")
# detail_analyse(result, 'lag_last_week_return', 't1_change_pct', None, None, 20, 20)
#





calculate_point_biserial_correlation(result, 't1_binary', 'fd_rate')